import pymysql
#   数据处理层：实现连接数据库，写数据库，读数据库
host = 'localhost'
port = 3308
user = 'root'
password = '123456'
database = 'market'
charset = 'utf8'
def get_sql():  #连接数据库
    db = pymysql.Connect(host=host, port=port, user=user, password=password, database=database, charset=charset)
    #   print("数据库已连接")
    return  db
def read_data(sql):
    db =get_sql()
    #   创建游标
    cursor = db.cursor()
    data = None
    try:
        cursor.execute(sql)
        data = cursor.fetchall()
        #print('记录已读取')
    except Exception as e:
        print(e)
    #   关闭游标
    cursor.close()
     #   关闭数据库
    db.close()
    return data
def writer_data(sql):
    db = get_sql()
    #   创建游标
    cursor = db.cursor()
    rows = 0
    try:
        rows = cursor.execute(sql)
        db.commit() #提交事务
    except Exception as e:
        db.rollback()   #回滚事务
        print(e)
    #   关闭游标
    cursor.close()
    #   关闭数据库
    db.close()
    return rows
if __name__ == '__main__':
    # 测试一下 get_sql()
     #print(get_sql())
    # # 测试一下 read_data()
    sql = "select * from products "
    data = read_data(sql)
    print(data)

    # 测试一下 write_data()
    # sql = "insert into products values (5, 1005, '榴莲', 9.00, 1.00)"
    # sql = "update products set price = '6' where name = '苹果'"
    # print(writer_data(sql))